import pymysql
import tkinter.messagebox
import tkinter as tk
from tkintertable import TableCanvas,TableModel
import re
class SqlTool:
    # 设置一个参数判断是否查询数据,1表示不是查询,默认不查询
    control=1
    def control_data(sql,args=None):
        #查询表的列名
        sql1="SELECT COLUMN_NAME FROM information_schema.COLUMNS where TABLE_SCHEMA='project' and TABLE_NAME = 'my_table';"
        #链接数据库
        con = pymysql.connect(host="localhost",port=3306,user='root',passwd='root',charset='utf8',db='project')
        #获取一个和数据库交互的游标
        coursor = con.cursor()
        if SqlTool.control==1:
            coursor.execute(sql,args)
            con.commit()
            coursor.close()
            con.close()
        if SqlTool.control==2:
            coursor.execute(sql,args)
            con.commit()
            coursor.close()
            con.close()
        else:
            coursor.execute(sql,args)
            #获取全部数据
            result=coursor.fetchall()
            return frame_select(data_list=result)
    #增加数据操作
    def add_data(name,own_class,phnum):
        """将数据添加到数据库中"""
        #将控制改为1
        SqlTool.control=1
        #编写增加语句
        sql="INSERT INTO my_table VALUES(0,%s,%s,%s);"
        args=(name,own_class,phnum)
        SqlTool.control_data(sql,args)
    #删除数据操作
    def del_data(name=None,own_class=None,phnum=None):
        SqlTool.control=1
        data_list=[]
        data_list.append(name)
        data_list.append(own_class)
        data_list.append(phnum)
        args=(name,own_class,phnum)
        #链接数据库
        con = pymysql.connect(host="localhost",port=3306,user='root',passwd='root',charset='utf8',db='project')
        #获取一个和数据库交互的游标
        coursor = con.cursor()
        sql="select * from my_table"
        coursor.execute(sql)
        #获取全部数据
        result=coursor.fetchall()
        if len(result)==0:
            NoData.no_data_display()
        else:
            #删除只有名字的数据
            if data_list[0]!='' and (data_list[1]=='' and data_list[2]==''):
                sql="DELETE from my_table where name=%s;"
                SqlTool.control_data(sql,args)
            #删除只有班级的数据
            if data_list[1]!='' and (data_list[0]=='' and data_list[2]==''):
                sql="DELETE from my_table where own_class=%s;"
                args=data_list[1]
                SqlTool.control_data(sql,args)
            #删除只有手机号的数据
            if data_list[2]!='' and (data_list[0]=='' and data_list[1]==''):
                sql="DELETE from my_table where phnum=%s;"
                args=data_list[2]
                SqlTool.control_data(sql,args)
            #删除有名字和班级的数据
            if data_list[0]!='' and data_list[1]!='' and data_list[2]=='':
                sql="DELETE from my_table where name=%s and own_class=%s;"
                args=(data_list[0],data_list[1])
                SqlTool.control_data(sql,args)
            #删除有名字和电话号码的数据
            if data_list[0]!='' and data_list[2]!='' and data_list[1]=='':
                sql="DELETE from my_table where name=%s and phnum=%s;"
                args=(data_list[0],data_list[2])
                SqlTool.control_data(sql,args)
            #删除有班级和电话号码的数据
            if data_list[1]!='' and data_list[2]!='' and data_list[0]=='':
                sql="DELETE from my_table where own_class=%s and phnum=%s;"
                args=(data_list[1],data_list[2])
                SqlTool.control_data(sql,args)
            #删除有名字班级和电话号码的数据
            if data_list[1]!='' and data_list[2]!='' and data_list[0]!='':
                sql="DELETE from my_table where name=%s and own_class=%s and phnum=%s;"
                args=(data_list[0],data_list[1],data_list[2])
                SqlTool.control_data(sql,args)
            if data_list[1]=='' and data_list[2]=='' and data_list[0]=='':
                #添加新的窗口
                Main.new_window=tk.Toplevel(Main.window)
                # 设置新窗口标题
                Main.new_window.title("确认删除全部数据吗！！")
                # 设置新窗口的大小
                Main.new_window.geometry('500x400')
                #设置窗口是否能控制
                Main.new_window.resizable(False,False)
                # 设置显示内容
                tk.Label(Main.new_window,text="确认删除全部数据吗?",font=('yahei',30)).place(relx=0.17,rely=0.1,relheight=0.2)
                def oe():
                    # 销毁窗口
                    Main.new_window.destroy()
                    sql="TRUNCATE TABLE my_table;"
                    SqlTool.control_data(sql)
                def new_window_close():
                    Main.new_window.destroy()
                #创建按钮
                btn = tk.Button(Main.new_window, text="确认",command=oe,fg="red",font=("kaiti",20))
                btn1 = tk.Button(Main.new_window, text="取消",command=new_window_close,fg="green",font=("kaiti",20))
                btn.place(relx=0.25,rely=0.5,height=60,width=120)
                btn1.place(relx=0.6,rely=0.5,height=60,width=120)

    #改数据操作
    def update_data(name=None,own_class=None,phnum=None,id=None):
        SqlTool.control=2
        sql="Update my_table set name=%s,own_class=%s,phnum=%s where id=%s;"
        args=(name,own_class,phnum,id)
        SqlTool.control_data(sql,args)
    #查询数据操作
    def select_data(name=None,own_class=None,phnum=None):
        SqlTool.control=0
        data_list=[]
        data_list.append(name)
        data_list.append(own_class)
        data_list.append(phnum)
        #查询只有名字的数据
        if data_list[0] and (data_list[1] and data_list[2])=='':
            sql="Select * from my_table where name=%s;"
            args=data_list[0]
        else:
            sql="select * from my_table"
            args=None
        #查询只有班级的数据
        if data_list[1] and (data_list[0] and data_list[2])=='':
            sql="Select * from my_table where own_class=%s;"
            args=data_list[1]
        #查询只有手机号的数据
        if data_list[2] and (data_list[0] and data_list[1])=='':
            sql="Select * from my_table where phnum=%s;"
            args=data_list[2]

        #查询有名字和班级的数据
        if data_list[0] and data_list[1] and data_list[2]=='':
            sql="Select * from my_table where name=%s and own_class=%s;"
            args=(data_list[0],data_list[1])
        #查询有名字和电话号码的数据
        if data_list[0] and data_list[2] and data_list[1]=='':
            sql="Select * from my_table where name=%s and phnum=%s;"
            args=(data_list[0],data_list[2])
        #查询有班级和电话号码的数据
        if data_list[1] and data_list[2] and data_list[0]=='':
            sql="Select * from my_table where own_class=%s and phnum=%s;"
            args=(data_list[1],data_list[2])
        #查询有名字班级和电话号码的数据
        if data_list[1] and data_list[2] and data_list[0]:
            sql="Select * from my_table where name=%s and own_class=%s and phnum=%s;"
            args=(data_list[0],data_list[1],data_list[2])
        SqlTool.control_data(sql,args)

class Main:
    Screen_width=1080
    Screen_height=720
    window=None
    new_window=None
    def __init__(self):
        #初始化主窗口
        Main.window=tk.Tk()
        #设置主窗口是否可变化
        Main.window.resizable(False,False)
        #设置窗口标题
        Main.window.title('第一次自己写项目')
        #设置窗口大小
        Main.window.geometry('%dx%d'%(Main.Screen_width,Main.Screen_height))
        #设置增加按钮
        self.adding_btn = tk.Button(Main.window, text="增加",command=self.add_new,bg='green',font=("songti",31))
        #设置删除按钮
        self.deleting_btn = tk.Button(Main.window, text="删除",command=self.delete_new,bg='red',font=("songti",31))
        #设置修改按钮
        self.update_btn = tk.Button(Main.window, text="修改",command=self.update_new,bg='blue',fg='red',font=("songti",31))
        #设置查询按钮
        self.select_btn = tk.Button(Main.window, text="查询",command=self.select_new,bg='yellow',font=("songti",31))
        #设置输入框
        self.entry_user_name=tk.Entry(Main.window,font=("kaiti",27),show="傻逼")
        self.entry_user_class=tk.Entry(Main.window,font=("kaiti",27))
        self.entry_user_phnum=tk.Entry(Main.window,font=("kaiti",27))
        #将按钮绑定函数
        self.adding_btn.bind('<Button-1>')
        self.deleting_btn.bind('<Button-1>')
        self.update_btn.bind('<Button-1>')
        self.select_btn.bind('<Button-1>')
    # 获取参数方法
    def get_data(self):
        #获取文本框内容
        self.uname=self.entry_user_name.get()
        self.own_class=self.entry_user_class.get()   
        self.phnum=self.entry_user_phnum.get()
        args={
            "name":self.uname,
            "own_class":self.own_class,
            "phnum":self.phnum
        }
        return args
    # 添加数据函数
    def add_new(self):
        args=self.get_data()
        # 判断数据是否正常
        if self.uname!='' and self.own_class!='' and re.match('1[3-9]\\d{9}',self.phnum):
            args={
                "name":self.uname,
                "own_class":self.own_class,
                "phnum":self.phnum
            }
            SqlTool.add_data(**args)
        else:
            return tkinter.messagebox.showerror(title="No!!",message="请输入正常数据！！")
    # 删除函数
    def delete_new(self):

        args=self.get_data()
        SqlTool.del_data(**args)
    # 修改函数
    def update_new(self):
        args=self.get_data()
        #链接数据库
        con = pymysql.connect(host="localhost",port=3306,user='root',passwd='root',charset='utf8',db='project')
        #获取一个和数据库交互的游标
        coursor = con.cursor()
        sql="select * from my_table"
        coursor.execute(sql)
        #获取全部数据
        result=coursor.fetchall()
        if len(result)==0:
            NoData.no_data_display()
        # 判断数据是否正常
        elif self.uname!='' and self.own_class!='' and re.match('1[3-9]\\d{9}',self.phnum):
            args={
                "name":self.uname,
                "own_class":self.own_class,
                "phnum":self.phnum
            }
            frame_update(**args)
        else:
            return tkinter.messagebox.showerror(title="No!!",message="请输入正常数据！！")
    # 查询函数
    def select_new(self):
        args=self.get_data()
        SqlTool.select_data(**args)
    def update_window(self):
        # 设置输入文本框的位置
        tk.Label(Main.window,text="姓名：",font=("kaiti",30)).place(x=200,y=30)
        self.entry_user_name.place(x=350,y=30,width=400,height=40)
        tk.Label(Main.window,text="班级：",font=("kaiti",30)).place(x=200,y=90)
        self.entry_user_class.place(x=350,y=95,width=400,height=40)
        tk.Label(Main.window,text="手机号：",font=("kaiti",30)).place(x=200,y=150,width=145)
        self.phnum=self.entry_user_phnum.place(x=350,y=160,width=400,height=40)
        # 设置按钮的显示
        self.adding_btn.place(x=300, y=250,height=60,width=80)
        self.deleting_btn.place(x=420, y=250,height=60,width=80)
        self.update_btn.place(x=540,y=250,height=60,width=80)
        self.select_btn.place(x=660,y=250,height=60,width=80)
        # 刷新窗口
        Main.window.mainloop()
def frame_select(data_list):
        # 所查询表的列内容
        title_list=['id','姓名','班级','电话']
        # 初始化数据
        data={}
        # 将数据存放到data中
        for i in range(len(data_list)):
            a=i+1
            data[f'row{a}']={}
            for b in range(len(data_list[i])):
                if b == 0:
                    data[f'row{a}'][title_list[0]]=data_list[i][b]
                if b == 1:
                    data[f'row{a}'][title_list[1]]=data_list[i][b]
                if b == 2:
                    data[f'row{a}'][title_list[2]]=data_list[i][b]
                if b == 3:
                    data[f'row{a}'][title_list[3]]=data_list[i][b]
        # 判断数据库中是否还有数据
        if len(data)==0:
            NoData.no_data_display()
        else:
            #添加新的窗口
            Main.new_window=tk.Toplevel(Main.window)
            #将新窗口加载出来
            frame1=tk.Frame(Main.new_window)
            # 设置新窗口标题
            Main.new_window.title("查询结果")
            # 设置新窗口的大小
            Main.new_window.geometry('%dx%d'%(len(title_list)*129,len(data_list)*90))
            # 放置frame,设置总表格宽度和高度
            frame1.place(x=0,y=0,width=len(title_list)*129,height=(len(data_list)+1)*90)
            # 初始化表格
            table = TableCanvas(frame1,data=data,read_only=True,rows=4,cellwidth=100,thefont=("kaiti",20),rowheight=30,vertlines=4)
            # 显示表格
            table.show()
class NoData:
    '''显示没有数据后的窗口'''
    def no_data_display():
        Main.new_window=tk.Toplevel(Main.window)
        # 设置新窗口标题
        Main.new_window.title("你在查什么？？？")
        # 设置新窗口的大小
        Main.new_window.geometry('500x400')
        Main.new_window.resizable(False,False)
        tk.Label(Main.new_window,text="仓库中已经没有数据了！！",font=('yahei',30,),fg='red').place(relx=0.04,rely=0.1,width=470)
        def new_window_close():
            Main.new_window.destroy()
        btn = tk.Button(Main.new_window, text="确认",command=new_window_close)
        btn.place(relx=0.35,rely=0.5,height=60,width=120)

def frame_update(name=None,own_class=None,phnum=None,id=None):
    #添加新的窗口
    Main.new_window=tk.Toplevel(Main.window)
    # 设置新窗口标题
    Main.new_window.title("查询结果")
    # 设置新窗口的大小
    Main.new_window.geometry('500x400')
    Main.new_window.resizable(False,False)
    tk.Label(Main.new_window,text="请输入你的id:",font=('yahei',30)).place(relx=0.25,rely=0.1,relheight=0.2)
    entry_user_id=tk.Entry(Main.new_window,font=("kaiti",30))
    entry_user_id.place(relx=0.25,rely=0.3,width=300,height=60)
    #设置查询按钮
    def oe():
        id=entry_user_id.get()
        if not id.isdigit():
            return tkinter.messagebox.showerror(title="No!!",message="请输入正常数据！！")
        else:
            args={
                "name":name,
                "own_class":own_class,
                "phnum":phnum,
                "id":id
            }
            Main.new_window.destroy()
            return SqlTool.update_data(**args)
    btn = tk.Button(Main.new_window, text="确认",command=oe)
    btn1 = tk.Button(Main.new_window, text="取消",command=new_window_close)
    btn.place(relx=0.25,rely=0.5,height=60,width=120)
    btn1.place(relx=0.6,rely=0.5,height=60,width=120)
    
def new_window_close():
    Main.new_window.destroy()

if __name__=='__main__':
    app=Main()
    app.update_window()


    